What is PostgREST
从字面上看 PostgREST 是 PostgreSQL+ RESTful的组合体，PostgreSQL是一个类似Mysql但比Mysql强大的多的关系性数据库。
PostgREST是haskell写的，以 postgres 数据库为基础，替你自动生成REST API。你只需要通过sql定义好数据库的table等，postgrest就能生成REST API。 PostgREST效率非常高，而且它连json的render 都在postgres中处理，性能反而还不错。
有这样的一张数据表结构
create table todos (
id serial primary key,
done boolean not null default false,
task text not null,
due timestamptz
);
insert into todos (task) values
('finish tutorial 0'), ('pat self on back');
运行PostgREST ，你就可以通过http访问数据库数据了
[
{
"id":1,
"done":false,
"task":"finish tutorial 0",
"due":null
},
{
"id":2,
"done":false,
"task":"pat self on back",
"due":null
}
]
安装
下载PostgREST打包好的releases版本就好
配置文件 db.conf
# db.conf
db-uri = "postgres://postgres:postgres@localhost:5432/eth"
db-schema = "public" # postgre default schema public
db-anon-role = "postgres" #用户
server-host = "0.0.0.0"
server-port = 4000 # 端口
设置PostgreSQL访问权限
在pg_hba.conf中配置服务端允许的认证方式
#/etc/postgresql/9.x/main/pg_hba.conf
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host all all 127.0.0.1/0 md5
重启数据库 sudo service postgresql restart
启动PostgREST
postgrest db.conf 根据配置文件中设定的端口启动http服务
请求数据
以下为真实数据
curl http://localhost:4000/blocks # 直接请求数据表 blocks
[
{
"id":1,
"height":10,
"timestamp":1516894509,
"txs_num":500,
"hash":"0x40b8e073a6196d330a35c4b41f8ec9e7a00aa4898d580ee360a703119517d986",
"parent_hash":"0x53d898401c6c7900cc0b14da33c2d25b1d4a83db36e7faefc998f597682ea708",
"uncle_hash":"0x1dcc4de8dec75d7aab85b567b6ccd41ad312451b948a7413f0a142fd40d49347",
"coinbase":"0x5a0b54d5dc17e0aadc383d2db43b0a0d3e029c4c",
"difficulty":236596,
"size":29216,
"gas_used":7965081,
"gas_limit":7988374,
"nonce":61634,
"reward":5193,
"extra_data":"sparkpool-cn-node-2",
}
]
查询条件可以灵活组合，生产力暴棚，更复杂的查询请看 PostgREST的API文档
http://localhost:4000/blocks?limit=10 #获取前10条数据
http://localhost:4000/blocks?limit=10&offset=30 #分页
http://localhost:4000/blocks?limit=10&order=height.desc #倒序
http://localhost:4000/blocks?limit=10&select=height #只获取height字段
http://localhost:4000/blocks?limit=10&select=height,hash&id.gte.99999 # 获取id>99999的数据
想法
以后出原型，可以这么做：
1 定义表数据结构
2 用postgREST 逆天神器生成RESTful API， 支持CURD
3 react 撸页面与API交互
新工具 pREST
[更新于 2018-07-20]
pREST 跟PostgREST功能类似。PostgREST 用haskell写的，pREST用go写的，文档也更全面。
pREST 例子
http://<url>/<db>/public/<tabel>?tokens=$notnull&balance=$gt.0&_page_size=20&_page=2
查询tabel中 tokens不为空 balance>0的数据，也支持分页，太棒了。
[更新于 2020-05-15]
prest.toml 配置文件内容：
# migrations = "./migrations"
# debug = true
# enabling debug mode will disable JWT authorization
[http]
port = 9000
# Port 6000 is blocked on windows. You must change to 8080 or any unblocked port
[jwt]
default = false
[pg]
host = "127.0.0.1"
user = "postgres"
pass = "postgres"
port = 5432
database = "zqw"
## or used cloud factor
# URL = "postgresql://user:pass@localhost/mydatabase/?sslmode=disable"
[access]
restrict = true # can access only the tables listed below
[[access.tables]]
name = "block"
permissions = ["read"]
fields="*"
参考:
http://postgrest.org/zh/latest/index.html
http://www.cnblogs.com/hiloves/archive/2011/08/20/2147043.html
https://github.com/prest/prest/
https://github.com/diogob/postgres-websockets
https://stackoverflow.com/a/24680845
https://github.com/dbohdan/automatic-api
